﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DAL
{
    public class QuerySQL
    {
        public static DataTable RealT_Frame0(DateTime time)
        {
            /*
             * SELECT   MeasurementInfo.Name, MeasurementInfo.CallNum, 温度数据.Temperature, 温度数据.CallInTime, 
                SystemFramework.Name AS Frame4
                FROM      MeasurementInfo INNER JOIN
                温度数据 ON MeasurementInfo.CallNum = 温度数据.Caller INNER JOIN
                SystemFramework ON MeasurementInfo.SuperiorID = SystemFramework.ID
                WHERE   (温度数据.CallInTime > CONVERT(DATETIME, '2014-02-28 17:00:00', 102))
             */
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT Top 50  MeasurementInfo.Name, MeasurementInfo.CallNum, SystemFramework.Name AS Frame4, 温度数据.Temperature,温度数据.CallInTime, MeasurementInfo.Address");
            sb.Append(" ");
            sb.Append("FROM      MeasurementInfo INNER JOIN");
            sb.Append(" ");
            sb.Append("温度数据 ON MeasurementInfo.CallNum = 温度数据.Caller INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework ON MeasurementInfo.SuperiorID = SystemFramework.ID");            
            sb.Append(" ");
            sb.Append("WHERE   (温度数据.CallInTime > CONVERT(DATETIME, '" + time.ToString("yyyy-MM-dd HH:mm:ss") + "', 102))");
            sb.Append(" ");
            sb.Append(" order by CallInTime desc");
            return WFNetLib.ADO.SQLServerOP.DataTableSQL(sb.ToString());
        }
        public static DataTable RealT_Frame1(DateTime time)
        {
            /*
             * SELECT   MeasurementInfo.Name, MeasurementInfo.CallNum, Level4.Name AS Frame4, 温度数据.Temperature, 
                温度数据.CallInTime
                FROM      MeasurementInfo INNER JOIN
                UserEx INNER JOIN
                SystemFramework AS Level1 INNER JOIN
                SystemFramework AS Level2 ON Level1.ID = Level2.SuperiorID INNER JOIN
                SystemFramework AS Level3 ON Level2.ID = Level3.SuperiorID INNER JOIN
                SystemFramework AS Level4 ON Level3.ID = Level4.SuperiorID ON UserEx.UserLevelID = Level1.ID ON 
                MeasurementInfo.SuperiorID = Level4.ID INNER JOIN
                温度数据 ON MeasurementInfo.CallNum = 温度数据.Caller
                WHERE   (温度数据.CallInTime > CONVERT(DATETIME, '2014-02-28 17:00:00', 102))
             */
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT Top 50  MeasurementInfo.Name, MeasurementInfo.CallNum, Level4.Name AS Frame4, 温度数据.Temperature,温度数据.CallInTime, MeasurementInfo.Address");
            sb.Append(" ");
            sb.Append("FROM      MeasurementInfo INNER JOIN");
            sb.Append(" ");
            sb.Append("UserEx INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Level1 INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Level2 ON Level1.ID = Level2.SuperiorID INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Level3 ON Level2.ID = Level3.SuperiorID INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Level4 ON Level3.ID = Level4.SuperiorID ON UserEx.UserLevelID = Level1.ID ON ");
            sb.Append(" ");
            sb.Append("MeasurementInfo.SuperiorID = Level4.ID INNER JOIN");
            sb.Append(" ");
            sb.Append("温度数据 ON MeasurementInfo.CallNum = 温度数据.Caller");
            sb.Append(" ");
            sb.Append("WHERE   (温度数据.CallInTime > CONVERT(DATETIME, '" + time.ToString("yyyy-MM-dd HH:mm:ss") + "', 102))");
            sb.Append(" ");
            sb.Append(" order by CallInTime desc");
            return WFNetLib.ADO.SQLServerOP.DataTableSQL(sb.ToString());
        }
        public static DataTable RealT_Frame2(DateTime time)
        {
            /*
             * SELECT   MeasurementInfo.Name, MeasurementInfo.CallNum, Level4.Name AS Frame4, 温度数据.Temperature, 
                温度数据.CallInTime
                FROM      温度数据 INNER JOIN
                MeasurementInfo INNER JOIN
                SystemFramework AS Level2 INNER JOIN
                SystemFramework AS Level3 ON Level2.ID = Level3.SuperiorID INNER JOIN
                SystemFramework AS Level4 ON Level3.ID = Level4.SuperiorID ON MeasurementInfo.SuperiorID = Level4.ID ON 
                温度数据.Caller = MeasurementInfo.CallNum INNER JOIN
                UserEx ON Level2.ID = UserEx.UserLevelID
                WHERE   (温度数据.CallInTime > CONVERT(DATETIME, '2014-02-28 17:00:00', 102))
             */
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT Top 50  MeasurementInfo.Name, MeasurementInfo.CallNum, Level4.Name AS Frame4, 温度数据.Temperature,温度数据.CallInTime, MeasurementInfo.Address");
            sb.Append(" ");
            sb.Append("FROM      MeasurementInfo INNER JOIN");
            sb.Append(" ");
            sb.Append("MeasurementInfo INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Level2 INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Level3 ON Level2.ID = Level3.SuperiorID INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Level4 ON Level3.ID = Level4.SuperiorID ON MeasurementInfo.SuperiorID = Level4.ID ON ");
            sb.Append(" ");
            sb.Append("温度数据.Caller = MeasurementInfo.CallNum INNER JOIN");
            sb.Append(" ");
            sb.Append("UserEx ON Level2.ID = UserEx.UserLevelID");
            sb.Append(" ");
            sb.Append("WHERE   (温度数据.CallInTime > CONVERT(DATETIME, '" + time.ToString("yyyy-MM-dd HH:mm:ss") + "', 102))");
            sb.Append(" ");
            sb.Append(" order by CallInTime desc");
            return WFNetLib.ADO.SQLServerOP.DataTableSQL(sb.ToString());
        }
        public static DataTable RealT_Frame3(DateTime time)
        {
            /*
             * SELECT Top 50  MeasurementInfo.Name, MeasurementInfo.CallNum, Level4.Name AS Frame4, 温度数据.Temperature, 
                温度数据.CallInTime
                FROM      温度数据 INNER JOIN
                MeasurementInfo INNER JOIN
                SystemFramework AS Level3 INNER JOIN
                SystemFramework AS Level4 ON Level3.ID = Level4.SuperiorID ON MeasurementInfo.SuperiorID = Level4.ID ON 
                温度数据.Caller = MeasurementInfo.CallNum INNER JOIN
                UserEx ON Level3.ID = UserEx.UserLevelID
                WHERE   (温度数据.CallInTime > CONVERT(DATETIME, '2014-02-28 17:00:00', 102))
             */
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT   MeasurementInfo.Name, MeasurementInfo.CallNum, Level4.Name AS Frame4, 温度数据.Temperature,温度数据.CallInTime, MeasurementInfo.Address");
            sb.Append(" ");
            sb.Append("FROM      MeasurementInfo INNER JOIN");
            sb.Append(" ");
            sb.Append("MeasurementInfo INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Level3 INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Level4 ON Level3.ID = Level4.SuperiorID ON MeasurementInfo.SuperiorID = Level4.ID ON ");
            sb.Append(" ");
            sb.Append("温度数据.Caller = MeasurementInfo.CallNum INNER JOIN");
            sb.Append(" ");
            sb.Append("UserEx ON Level2.ID = UserEx.UserLevelID");
            sb.Append(" ");
            sb.Append("WHERE   (温度数据.CallInTime > CONVERT(DATETIME, '" + time.ToString("yyyy-MM-dd HH:mm:ss") + "', 102))");
            sb.Append(" ");
            sb.Append(" order by CallInTime desc");
            return WFNetLib.ADO.SQLServerOP.DataTableSQL(sb.ToString());
        }
        public static DataTable RealT_Frame4(DateTime time)
        {
            /*
             * SELECT   MeasurementInfo.Name, MeasurementInfo.CallNum, Level4.Name AS Frame4, 温度数据.Temperature, 
                温度数据.CallInTime
                FROM      温度数据 INNER JOIN
                MeasurementInfo INNER JOIN
                SystemFramework AS Level4 ON MeasurementInfo.SuperiorID = Level4.ID ON 
                温度数据.Caller = MeasurementInfo.CallNum INNER JOIN
                UserEx ON Level4.ID = UserEx.UserLevelID
                WHERE   (温度数据.CallInTime > CONVERT(DATETIME, '2014-02-28 17:00:00', 102))
             */
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT Top 50  MeasurementInfo.Name, MeasurementInfo.CallNum, Level4.Name AS Frame4, 温度数据.Temperature,温度数据.CallInTime, MeasurementInfo.Address");
            sb.Append(" ");
            sb.Append("FROM      MeasurementInfo INNER JOIN");
            sb.Append(" ");
            sb.Append("MeasurementInfo INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Level4 ON MeasurementInfo.SuperiorID = Level4.ID ON ");
            sb.Append(" ");
            sb.Append("温度数据.Caller = MeasurementInfo.CallNum INNER JOIN");
            sb.Append(" ");
            sb.Append("UserEx ON Level2.ID = UserEx.UserLevelID");
            sb.Append(" ");
            sb.Append("WHERE   (温度数据.CallInTime > CONVERT(DATETIME, '" + time.ToString("yyyy-MM-dd HH:mm:ss") + "', 102))");
            sb.Append(" ");
            sb.Append(" order by CallInTime desc");
            return WFNetLib.ADO.SQLServerOP.DataTableSQL(sb.ToString());
        }

        public static int GetUserFrameLevel(Guid id)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT   SystemFramework.FrameLevel");
            sb.Append(" ");
            sb.Append("FROM      UserEx INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework ON UserEx.UserLevelID = SystemFramework.ID");
            sb.Append(" ");
            sb.Append("WHERE   (UserEx.UserID = '"+id.ToString()+"')");
            DataTable dt = WFNetLib.ADO.SQLServerOP.DataTableSQL(sb.ToString());
            if (dt.Rows.Count == 0)
                return -1;
            else
                return (int)dt.Rows[0][0];
        }

        public static bool CheckCallNumAuth_Frame1(string CallNum, Guid userid)
        {
//             SELECT UserEx.UserName
// FROM MeasurementInfo INNER JOIN
//       SystemFramework AS Frame4 ON 
//       MeasurementInfo.SuperiorID = Frame4.ID INNER JOIN
//       SystemFramework AS Frame3 ON Frame4.SuperiorID = Frame3.ID INNER JOIN
//       SystemFramework AS Frame2 ON Frame3.SuperiorID = Frame2.ID INNER JOIN
//       SystemFramework AS Frame1 ON Frame2.SuperiorID = Frame1.ID INNER JOIN
//       UserEx ON Frame1.ID = UserEx.UserLevelID
// WHERE (MeasurementInfo.CallNum = '045187722360') AND (UserEx.UserID = '123')
// GROUP BY UserEx.UserName            
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT UserEx.UserName");
            sb.Append(" ");
            sb.Append("FROM MeasurementInfo INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Frame4 ON ");
            sb.Append(" ");
            sb.Append("MeasurementInfo.SuperiorID = Frame4.ID INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Frame3 ON Frame4.SuperiorID = Frame3.ID INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Frame2 ON Frame3.SuperiorID = Frame2.ID INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Frame1 ON Frame2.SuperiorID = Frame1.ID INNER JOIN");
            sb.Append(" ");
            sb.Append("UserEx ON Frame1.ID = UserEx.UserLevelID");
            sb.Append(" ");
            sb.Append("WHERE (MeasurementInfo.CallNum = '"+CallNum+"') AND (UserEx.UserID = '"+userid.ToString()+"')");
            sb.Append(" ");
            sb.Append("WHERE (MeasurementInfo.CallNum = '045187722360') AND (UserEx.UserID = '123')");
            int x= WFNetLib.ADO.SQLServerOP.NonQuerySQL(sb.ToString());
            if (x != 0)
                return false;
            else
                return true;
        }


        public static bool CheckCallNumAuth_Frame2(string CallNum, Guid userid)
        {
            //             SELECT UserEx.UserName
            // FROM MeasurementInfo INNER JOIN
            //       SystemFramework AS Frame4 ON 
            //       MeasurementInfo.SuperiorID = Frame4.ID INNER JOIN
            //       SystemFramework AS Frame3 ON Frame4.SuperiorID = Frame3.ID INNER JOIN
            //       SystemFramework AS Frame2 ON Frame3.SuperiorID = Frame2.ID INNER JOIN
            //       SystemFramework AS Frame1 ON Frame2.SuperiorID = Frame1.ID INNER JOIN
            //       UserEx ON Frame1.ID = UserEx.UserLevelID
            // WHERE (MeasurementInfo.CallNum = '045187722360') AND (UserEx.UserID = '123')
            // GROUP BY UserEx.UserName            
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT UserEx.UserName");
            sb.Append(" ");
            sb.Append("FROM MeasurementInfo INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Frame4 ON ");
            sb.Append(" ");
            sb.Append("MeasurementInfo.SuperiorID = Frame4.ID INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Frame3 ON Frame4.SuperiorID = Frame3.ID INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Frame2 ON Frame3.SuperiorID = Frame2.ID INNER JOIN");
            sb.Append(" ");
            sb.Append("UserEx ON Frame2.ID = UserEx.UserLevelID");
            sb.Append(" ");
            sb.Append("WHERE (MeasurementInfo.CallNum = '" + CallNum + "') AND (UserEx.UserID = '" + userid.ToString() + "')");
            sb.Append(" ");
            sb.Append("WHERE (MeasurementInfo.CallNum = '045187722360') AND (UserEx.UserID = '123')");
            int x = WFNetLib.ADO.SQLServerOP.NonQuerySQL(sb.ToString());
            if (x != 0)
                return false;
            else
                return true;
        }

        public static bool CheckCallNumAuth_Frame3(string CallNum, Guid userid)
        {
            //             SELECT UserEx.UserName
            // FROM MeasurementInfo INNER JOIN
            //       SystemFramework AS Frame4 ON 
            //       MeasurementInfo.SuperiorID = Frame4.ID INNER JOIN
            //       SystemFramework AS Frame3 ON Frame4.SuperiorID = Frame3.ID INNER JOIN
            //       SystemFramework AS Frame2 ON Frame3.SuperiorID = Frame2.ID INNER JOIN
            //       SystemFramework AS Frame1 ON Frame2.SuperiorID = Frame1.ID INNER JOIN
            //       UserEx ON Frame1.ID = UserEx.UserLevelID
            // WHERE (MeasurementInfo.CallNum = '045187722360') AND (UserEx.UserID = '123')
            // GROUP BY UserEx.UserName            
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT UserEx.UserName");
            sb.Append(" ");
            sb.Append("FROM MeasurementInfo INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Frame4 ON ");
            sb.Append(" ");
            sb.Append("MeasurementInfo.SuperiorID = Frame4.ID INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Frame3 ON Frame4.SuperiorID = Frame3.ID INNER JOIN");
            sb.Append(" ");
            sb.Append("UserEx ON Frame3.ID = UserEx.UserLevelID");
            sb.Append(" ");
            sb.Append("WHERE (MeasurementInfo.CallNum = '" + CallNum + "') AND (UserEx.UserID = '" + userid.ToString() + "')");
            sb.Append(" ");
            sb.Append("WHERE (MeasurementInfo.CallNum = '045187722360') AND (UserEx.UserID = '123')");
            int x = WFNetLib.ADO.SQLServerOP.NonQuerySQL(sb.ToString());
            if (x != 0)
                return false;
            else
                return true;
        }

        public static bool CheckCallNumAuth_Frame4(string CallNum, Guid userid)
        {
            //             SELECT UserEx.UserName
            // FROM MeasurementInfo INNER JOIN
            //       SystemFramework AS Frame4 ON 
            //       MeasurementInfo.SuperiorID = Frame4.ID INNER JOIN
            //       SystemFramework AS Frame3 ON Frame4.SuperiorID = Frame3.ID INNER JOIN
            //       SystemFramework AS Frame2 ON Frame3.SuperiorID = Frame2.ID INNER JOIN
            //       SystemFramework AS Frame1 ON Frame2.SuperiorID = Frame1.ID INNER JOIN
            //       UserEx ON Frame1.ID = UserEx.UserLevelID
            // WHERE (MeasurementInfo.CallNum = '045187722360') AND (UserEx.UserID = '123')
            // GROUP BY UserEx.UserName            
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT UserEx.UserName");
            sb.Append(" ");
            sb.Append("FROM MeasurementInfo INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework AS Frame4 ON ");
            sb.Append(" ");
            sb.Append("MeasurementInfo.SuperiorID = Frame4.ID INNER JOIN");
            sb.Append(" ");
            sb.Append("UserEx ON Frame4.ID = UserEx.UserLevelID");
            sb.Append(" ");
            sb.Append("WHERE (MeasurementInfo.CallNum = '" + CallNum + "') AND (UserEx.UserID = '" + userid.ToString() + "')");
            sb.Append(" ");
            sb.Append("WHERE (MeasurementInfo.CallNum = '045187722360') AND (UserEx.UserID = '123')");
            int x = WFNetLib.ADO.SQLServerOP.NonQuerySQL(sb.ToString());
            if (x != 0)
                return false;
            else
                return true;
        }
        public static DataTable GetT_CallNum(string CallNum,DateTime dt1,DateTime dt2)
        {
// SELECT 温度数据.Caller AS 号码, MeasurementInfo.Name AS 名称, 
//       MeasurementInfo.Address AS 地址, SystemFramework.Name AS 供热单位, 
//       温度数据.Temperature AS 温度, 温度数据.CallInTime AS 时间
// FROM 温度数据 INNER JOIN
//       MeasurementInfo ON 温度数据.Caller = MeasurementInfo.CallNum INNER JOIN
//       SystemFramework ON MeasurementInfo.SuperiorID = SystemFramework.ID
// GROUP BY 温度数据.CallInTime, 温度数据.Temperature, MeasurementInfo.Name, 
//       MeasurementInfo.Address, SystemFramework.Name, 温度数据.Caller
// HAVING (温度数据.CallInTime BETWEEN CONVERT(DATETIME, '2013-04-05 22:00:00', 102) 
//       AND CONVERT(DATETIME, '2014-07-16 23:00:00', 102)) AND 
//       (温度数据.Caller = '045187722360')
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT 温度数据.Caller AS 号码, MeasurementInfo.Name AS 名称,MeasurementInfo.Address AS 地址, SystemFramework.Name AS 供热单位,温度数据.Temperature AS 温度, 温度数据.CallInTime AS 时间");
            sb.Append(" ");
            sb.Append("FROM 温度数据 INNER JOIN");
            sb.Append(" ");
            sb.Append("MeasurementInfo ON 温度数据.Caller = MeasurementInfo.CallNum INNER JOIN");
            sb.Append(" ");
            sb.Append("SystemFramework ON MeasurementInfo.SuperiorID = SystemFramework.ID");
            sb.Append(" ");
            sb.Append("GROUP BY 温度数据.CallInTime, 温度数据.Temperature, MeasurementInfo.Name,");
            sb.Append(" ");
            sb.Append("MeasurementInfo.Address, SystemFramework.Name, 温度数据.Caller");
            sb.Append(" ");
            sb.Append("HAVING (温度数据.CallInTime BETWEEN CONVERT(DATETIME, '" + dt1.ToString("yyyy-MM-dd HH:mm:ss") + "', 102)");
            sb.Append(" ");
            sb.Append("AND CONVERT(DATETIME, '" + dt2.ToString("yyyy-MM-dd HH:mm:ss") + "', 102)) AND");
            sb.Append(" ");
            sb.Append("(温度数据.Caller = '"+CallNum+"')");
            sb.Append(" ");            
            sb.Append(" order by CallInTime desc");
            return WFNetLib.ADO.SQLServerOP.DataTableSQL(sb.ToString());
        }
    }
}
